package utils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.Query;

import models.Excel;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import play.db.jpa.JPA;
import play.db.jpa.Model;

public class ExcleJson {
	/**
	 * 获取数据库记录里的时间(不重复的)
	 * 
	 * @return
	 */
	@SuppressWarnings("null")
	public static String getTimeNumber() {
		Map<String, Object> option = new HashMap<>();
		Map<String, Object> legend = new HashMap<>();
		List<String> data = new ArrayList<>();
		data.add("机构买单");
		data.add("机构吃货");
		option.put("legend", data);
		
		Map<String, Object> xAxis = new HashMap<>();
		Map<String, Object> type = new HashMap<>();
		String sql = "select distinct time from Excel";// 创建sql查询
		Query query = JPA.em().createQuery(sql);// 查询结果返回至实体对象question
		List<String> Times = query.getResultList();
		System.out.println(Times.size());
		option.put("xAxis", Times);

		List<Long> seriestimedata = new ArrayList<>();
		List<Long> seriestimedata2 = new ArrayList<>();
		List<Map<String, Object>> serieslist = new ArrayList<>();
		Long result =999L;
		for (int i = 0; i < Times.size(); i++) {
			sql = "SELECT sum(number) FROM Excel WHERE time = '" + Times.get(i) + "' AND type='机构买单'";
			//System.out.println(sql);
			query = JPA.em().createQuery(sql);
			result =  (Long) query.getResultList().get(0);
			//System.out.println(result);
			seriestimedata.add(result);
			sql = "SELECT sum(number) FROM Excel WHERE time = '" + Times.get(i) + "' AND type='机构吃货'";
			//System.out.println(sql);
			query = JPA.em().createQuery(sql);
			result =  (Long) query.getResultList().get(0);
			//System.out.println(result);
			seriestimedata2.add(result);
		}
		option.put("series1", seriestimedata);
		option.put("series2", seriestimedata2);
		String optionstr = JSONObject.fromObject(option).toString();
		System.out.println(optionstr);
		return optionstr;
	}

	public static String timeScore() {
		return "";
	}
}
